﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetMemberInClass_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetMemberInClass_V2];
GO
CREATE PROCEDURE [dbo].[sproc_GetMemberInClass_V2]
    @ClassID int
/*

============================================================
功能:    得到组的所有成员
参数:
    @ClassID int        :    组(类)ID
============================================================

*/

AS
BEGIN
SET NOCOUNT ON;

--·@ClassID <= 0 时为所有的成员
IF @ClassID <= 0
BEGIN
    SELECT 
            S.*,
            P.Position_ID,
            P.Position_name
        FROM
            dbo.UDS_Staff S
            INNER JOIN dbo.UDS_Staff_In_Position SIP ON S.Staff_ID = SIP.Staff_ID
            INNER JOIN dbo.UDS_Position P ON SIP.Position_ID = P.Position_ID
        WHERE S.Dimission = 0;
END
ELSE
BEGIN
    SELECT
            S.*,
            P.Position_ID,
            P.Position_name
        FROM
            dbo.UDS_Staff S
            INNER JOIN dbo.UDS_Staff_In_Team SIT ON S.Staff_ID = SIT.Staff_ID
            INNER JOIN dbo.UDS_Staff_In_Position SIP ON S.Staff_ID = SIP.Staff_ID
            INNER JOIN dbo.UDS_Position P ON SIP.Position_ID = P.Position_ID
        WHERE
            S.Dimission = 0
            AND SIT.Team_ID = @ClassID;
END

END
GO
